テクサポと学ぶ PostgreSQL #5 psql
2024.10.29アノテーション、Postgre 大好き村上です。
シリーズ第 4 回目 テクサポと学ぶ PostgreSQL #4 createuser では、createuser ユーティリティを利用してのユーザー作成を試してみました。シリーズ第 5 回目は、開発環境等で利用する機会が多い psql ツールをご紹介します。
本シリーズでしていくこと
PostgreSQL に対しての基本的な知識を確認しながら、時には EC2 上にインストールした PostgreSQL 15 において DDL や DML 等のコマンドを実行して動作確認をしていきます。また、業務で RDS for PostgreSQL や Aurora PostgreSQL へのお問い合わせに対応しているので、設定や運用面でお役に立つような情報も合わせてご紹介していきます。
psql とは？
psqlとはPostgreSQLのターミナル型フロントエンドです。 対話的に問い合わせを入力し、それをPostgreSQLに対して発行して、結果を確認することができます。 また、ファイルまたはコマンドライン引数から入力を読み込むことも可能です。 さらに、psqlは、スクリプトの記述を簡便化したり、様々なタスクを自動化したりする、いくつものメタコマンドとシェルに似た各種の機能を備えています。
psql は、PostgreSQL のデータベースへ接続し SQL コマンドを実行するための対話型ターミナルツールです。クエリの実行、データベース管理、スクリプト実行が可能で、開発やデバッグによく利用されます。シリーズ第 4 回目で取り上げた createuser ユーティリティと同様に、UNIX ドメインでのローカル接続とクライアントからのリモート接続が可能です。以下の表は、リモート接続時によく利用するオプションの説明となります。
| オプション（省略形） | オプション | 説明 |
|---|---|---|
| -U username | --username=username | デフォルトのユーザではなくusernameユーザとしてデータベースに接続します （当然、そうする権限を持っていなければなりません）。 |
| -h host | --host=host | サーバが稼働しているマシンのホスト名を指定します。 この値がスラッシュから始まる場合、Unixドメインソケット用のディレクトリとして使用されます。 |
| -p port | --port=port | サーバが接続監視を行っているTCPポートもしくはローカルUnixドメインソケットファイルの拡張子を指定します。 環境変数PGPORTの値、環境変数が設定されていない場合はコンパイル時に指定した値（通常は5432）がデフォルト値となります。 |
| -d dbname | --dbname=dbname | 接続するデータベースの名前を指定します。 コマンドラインでオプション以外の最初の引数としてdbnameを指定するのと同じ効力を持ちます。 dbnameは接続文字列でも構いません。 その場合、接続文字列パラメータは衝突するコマンドラインオプションに優先します。 |
psql 接続時に接続先のデータベースを指定しない場合は、接続で利用しているユーザー名と同名のデータベースへの接続を試みます。ユーザー名と同名のデータベースが存在しない場合は、デフォルトデータベース（多くの場合 postgres）へ接続を試みます。
$ psql -U remoteuser -h 10.0.30.28 -p 5432 -d devdb
psql でよく利用するオプション
psql でよく利用するオプションを、PostgreSQL ドキュメントから一部抜粋します。
| オプション（省略形） | オプション | 説明 |
|---|---|---|
| -c command | --command=command | psqlに対し、指定のコマンド文字列commandを実行するよう指示します。 このオプションは繰り返すことができ、また-fオプションと任意の順序で組み合わせることができます。 -cまたは-fが指定されると、psqlは標準入力からコマンドを読み取りません。 その代わりに、すべての-cオプションおよび-fオプションを順に処理した後、終了します。 |
| -f filename | --file=filename | 標準入力ではなく、ファイルfilenameからコマンドを読み取ります。 このオプションは繰り返すことができ、また-cオプションと任意の順序で組み合わせることができます。 -cまたは-fが指定されると、psqlは標準入力からコマンドを読み取りません。 その代わりに、すべての-cオプションおよび-fオプションを順に処理した後、終了します。 |
| -l | --list | 利用可能な全てのデータベースを一覧表示し、終了します。 この他の接続に関連しないオプションは無視されます。 \listメタコマンドと似た効力を持ちます。 |
| -V | --version | psqlのバージョンを表示し、終了します。 |
$ psql -c 'SELECT * FROM sampletable;'
id | first name | registration date
----+------------+-------------------
1 | Emi | 2023-03-21
2 | Taro | 2023-04-05
3 | Yutaka | 2023-05-17
(3 rows)
psql でよく利用するメタコマンド
psql 接続中によく利用するメタコマンドを、PostgreSQL ドキュメントから一部抜粋します。メタコマンドって何？という方は、短縮形の便利コマンドと思っていただければ大丈夫です。これらのコマンドは、通常の SQL コマンドとは異なり、psql クライアント内で特別な機能を提供します。
メタコマンド
psql内で入力されたコマンドのうち、引用符で囲まれていないバックスラッシュで始まるものは、psql自身が実行するpsqlのメタコマンドとして扱われます。 これらのコマンドを使うと、データベースを管理したりスクリプトを作成するにあたって、psqlがより便利になります。 メタコマンドはよくスラッシュコマンド、またはバックスラッシュコマンドとも呼ばれます。
| メタコマンド | 説明 |
|---|---|
| \copy | フロントエンド（クライアント）コピーを行います。 これはCOPY SQLコマンドを実行する操作ですが、サーバで指定ファイルに対する読み込みまたは書き込みを行うのではなく、psqlがファイルの読み書きや、サーバとローカルファイルシステム間のデータ送信を行います。 この場合、ファイルへのアクセス権限はサーバではなくローカルユーザのものを使用するので、SQLのスーパーユーザ権限は必要ありません。 |
| \d [ pattern ] | patternにマッチする各リレーション（テーブル、ビュー、マテリアライズドビュー、インデックス、シーケンス、外部テーブル）または複合型について、全ての列、列の型、テーブル空間（デフォルト以外を使用している場合）、NOT NULLやデフォルトなどの特別な属性を表示します。 関連付けられているインデックス、制約、ルールおよびトリガも表示されます。 外部テーブルでは関連する外部サーバも表示されます。 |
| \dp | テーブル、ビュー、シーケンスを、関連付けられているアクセス権限とともに一覧表示します。 patternを指定すると、パターンに名前がマッチするテーブル、ビュー、シーケンスのみが表示されます。 |
| \du | データベースロールを一覧表示します。 |
| \l または \list | サーバ内のデータベースについて、その名前、所有者、文字セット符号化方式、アクセス権限を一覧表示します。 patternを指定すると、パターンにマッチする名前を持つデータベースのみを表示します。 コマンド名に+を付けると、データベースのサイズ、デフォルトのテーブル空間、説明も表示します。 |
| \password [ username ] | 指定したユーザ（デフォルトは現在のユーザ）のパスワードを変更します。 このコマンドは、新しいパスワードを促し、暗号化して、それをALTER ROLEコマンドとしてサーバに送信します。 これによりコマンド履歴やサーバログなどどこにも新しいパスワードが平文では残りません |
| \qまたは\quit | psqlプログラムを終了します。 |
| \timing [ on 、off ] | パラメータがない場合、各SQL文にかかる時間（ミリ秒単位）の表示の有無を切り替えます。 パラメータがある場合、指定した通りに設定します。 |
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+---------+---------+------------+-----------------+-----------------------
postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | libc |
template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
(3 rows)
ファイルを指定してバッチ処理をやってみた
psql のオプション -f（filename）を利用して、ファイルを指定したバッチ処理を実行してみます。具体的には、以下の処理を順番に実行します。
- テーブル sampletable を作成
- テーブル sampletable に対して 3 行のレコードを挿入
- sampletable の全レコードを標準出力
$ cat batch_commands.sql
-- batch_commands.sql の内容
CREATE TABLE sampletable (
id SERIAL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
registration_date DATE NOT NULL
);
INSERT INTO sampletable (first_name, registration_date) VALUES
('Emi', '2023-03-21'),
('Taro', '2023-04-05'),
('Yutaka', '2023-05-17');
SELECT * FROM sampletable;
UNIX ドメインでのローカル接続の場合は、上記ファイルをパス /var/lib/pgsql へ保存します。（本ブログではパッケージを利用して EC2 上に PostgreSQL 15 を作成しています。）
$ psql -U postgres -d postgres -f batch_commands.sql
$ psql -U postgres -d postgres -f batch_commands.sql
Password for user postgres:
CREATE TABLE
INSERT 0 3
id | first_name | registration_date
----+------------+-------------------
1 | Emi | 2023-03-21
2 | Taro | 2023-04-05
3 | Yutaka | 2023-05-17
(3 rows)
テーブル作成、レコードの挿入、テーブルの全レコードを SELECT して標準出力という一連の処理を実行できました！
まとめ
本記事では、私もよく利用する psql ツールについてご紹介してみました。本記事で紹介した以外にもオプションはまだまだあるので、ぜひ試してみてください。
本シリーズを通して、PostgreSQL の基本的な設定や機能を一緒に学んでいければ嬉しいです。
この記事がどなたかのお役に立てば幸いです。
参考資料
アノテーション株式会社について
アノテーション株式会社はクラスメソッドグループのオペレーション専門特化企業です。サポート・運用・開発保守・情シス・バックオフィスの専門チームが、最新 IT テクノロジー、高い技術力、蓄積されたノウハウをフル活用し、お客様の課題解決を行っています。当社は様々な職種でメンバーを募集しています。「オペレーション・エクセレンス」と「らしく働く、らしく生きる」を共に実現するカルチャー・しくみ・働き方にご興味がある方は、アノテーション株式会社 採用サイトをぜひご覧ください。
